# 编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
import openpyxl
import pymysql

wb = openpyxl.Workbook('user.xlsx')
ws = wb.create_sheet(title='info')
ws.append(
    ['user_id', 'idcard', 'username', 'real_name', 'pwd', 'tel_phone', 'email', 'age', 'sex', 'address', 'hire_date',
     'sal', 'job', 'company'])
for i in range(1, 2001):
    user_id = i
    idcard = '123456'
    username = 'root'
    real_name = 'Alice'
    pwd = '123456'
    tel_phone = '123456789'
    email = '123456@mail.com'
    age = 18
    sex = 'female'
    address = 'China'
    hire_date = '2024.10.31'
    sal = 5000.00
    job = 'programmer'
    company = 'bank'
    ws.append(
        [user_id, idcard, username, real_name, pwd, tel_phone, email, age, sex, address, hire_date, sal, job, company])
wb.save('user.xlsx')
wb.close()
# 在创建工作簿的时候，不能够同时读写数据.把工作簿创建好，保存，然后再打开，数据就可以读写了。
workbook = openpyxl.load_workbook('user.xlsx')
sheet = workbook['info']
db = pymysql.connect(host='localhost', user='root', password='123456', database='testCase.py', port=3306, charset='utf8')
cursor = db.cursor()
for i in sheet[2:2001]:
    list0 = []
    for j in i:
        list0.append(j.value)
    sql = 'insert into user_info values' + str(tuple(list0))
    cursor.execute(sql)
db.commit()
cursor.close()
db.close()
